#!/usr/bin/env python
# -*- coding: utf-8 -*-
# EkikaraDb
# 2010/12/28 運転日注意対応
#            列車番号はユニークである前提で来たが、運転日注意の場合は
#            この前提が崩れるので特定するためにURLをキーに加えるよう変更開始。
# 2010/09/10 ループ線未対応
#            ループ線の場合同一駅名が2度出てくる事があるが、今回は未対応。
#            全ての駅名はユニークである前提の元にCSV生成する。
# 2010/09/17 会社名、線名追加
#            会社名、線名を追加し、テーブル構造を作り直す。任意の線区が指定されてもDBへ格納できるようにする。

import sqlite3
import pprint
import MergeStationError
import MergeEkiError
import InvalidTimetableError

TIMETABLE_ATTR_SQL = u"""
create table timetable_attr (
        timetable_id int NOT NULL,
        company text NOT NULL,
        railway_line text NOT NULL,     -- if nothing ''. DO NOT USE null.
        updown text NOT NULL,   -- 'down' or 'up'
        day text NOT NULL,      -- 平日、休日、土休日、9999年99月など
        direction text,
        note text,
        primary key(timetable_id)
);
"""

TIMETABLE_ATTR_INDEX_SQL = u"""
create unique index timetable_attr_index on timetable_attr (company, railway_line, updown, day);
"""

# 指定線区の駅名順リスト。
# 下りを基準とする。上りが無い場合は下りの逆順を使う(order by station_order desc)
LINE_STATION_SQL = u"""
create table railway_line_station (
        timetable_id int NOT NULL,
        station_order int NOT NULL,
        station_name text,
        display_arrival int default 0,      -- 0 or 1
        display_departure int default 0,    -- 0 or 1
        primary key(timetable_id, station_order)
);
"""

# 運転日注意の場合、列車番号が同一であるが、URLが異なるものが存在するため、キーに加える。
TRAIN_HEADER_SQL = u"""
create table train_header (
        timetable_id int NOT NULL,
        train_number text NOT NULL,     -- 列車番号。その線区・向き(上りelse下り)でユニーク
        train_order int,        -- 並び順を決定
        train_name text,
        train_type text,        -- 特急, 快速, etc.
        note text,      -- 備考
        date text,      -- 運転日(運転日注意等)
        gou text,       -- n号(特急,etc)
        url text NOT NULL,       -- 当該列車を抽出したURL
        train_type_prefix text, -- 列車種別の接頭辞(「Ｌ」特急、「特別」快速 etc.)
        primary key(timetable_id, train_number, url)
);
"""

TRAIN_HEADER_INDEX_SQL = u"""
create index train_header_index on train_header ( timetable_id, train_order )
"""


# 当該列車の着発駅を記述(表示線区より長い事もある)
# URL追加
# train_time が更新されたら駅名がずれてないかどうか再チェック・再構築の必要がある。
TRAIN_TIME_SQL = u"""
create table train_time  (
        timetable_id int NOT NULL,
        train_number text NOT NULL,
        url text NOT NULL,
        station_order int NOT NULL,
        station_name text,
        train_arrival text,     -- 着時刻(99:99)または通過'レ'
        train_departure text,   -- 発時刻(99:99)または通過'レ'
        platform text,  -- 発着番線
        primary key(timetable_id, train_number, url, station_order)
);
"""

TRAIN_TIME_INDEX_SQL = u"""
create unique index train_time_index on train_time (timetable_id asc,
                                                    train_number asc,
                                                    url asc,
                                                    station_order asc)
"""

#時刻表CSV出力時に左側に出力される部分
#環状線等の場合は同一駅名が2回出現するので駅名をキーにはしない
TITLE_COLUMN_SQL = u"""
create table title_column (
        timetable_id int NOT NULL,
        row integer,        -- line number
        title text,     -- item name and station name
        arrival_departure text,  -- 'arrival' or 'departure'
        station_index int,
        primary key(timetable_id, row)
);
"""

#OuDia Eki list work table
OUDIA_EKI_SQL = u"""
create table oudia_eki (
        eki_order int NOT NULL,
        eki_name text,
        down_arrival int,
        down_departure int,
        up_arrival int,
        up_departure int,
        primary key(eki_order)
);
"""

OUDIA_EKI_INDEX_SQL = u"""
create index oudia_eki_index on oudia_eki ( eki_name )
"""

#路線駅と列車停車駅の対応を保持
#TRAIN_TIME_SQLとOUDIA_EKI_SQLの関連付け
TRUE_TRAIN_STOP_SQL = u"""
create table true_train_stop (
        timetable_id int NOT NULL,
        train_number text NOT NULL,
        url text NOT NULL,
        station_order int NOT NULL,
        eki_order int NOT NULL,
        primary key(timetable_id, train_number, url, station_order, eki_order)
);
"""

DELETE_TRUE_TRAIN_STOP_SQL = u"""
delete from true_train_stop
"""

INSERT_TIMETABLE_ATTR_SQL = u"""
insert into timetable_attr (timetable_id, company, railway_line, updown, day, direction, note)
values(?, ?, ?, ?, ?, ?, ?);
"""

INSERT_RAILWAY_LINE_STATION_SQL = u"""
insert into railway_line_station(timetable_id, station_order, station_name, display_arrival, display_departure)
values(?, ?, ?, ?, ?);
"""

INSERT_TITLE_COLUMN_SQL = u"""
insert into title_column(timetable_id, row, title, arrival_departure, station_index)
values(?, ?, ?, ?, ?);"""

INSERT_TRAIN_HEADER_SQL = u"""
insert into train_header(timetable_id, train_number, train_order, train_name, train_type, note, date, gou, url, train_type_prefix)
values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
"""

INSERT_TRAIN_TIME_SQL = u"""
insert into train_time (timetable_id,  train_number,  url, station_order,  station_name,  train_arrival,  train_departure,  platform)
values (?, ?, ?, ?, ?, ?, ?, ?);
"""

SELECT_TITLE_COLUMN_SQL = u"""
select
        row,
        title,
        arrival_departure,
        station_index
from title_column
where timetable_id = ?
order by row;
"""

SELECT_TRAIN_NUMBER_LIST_SQL = u"""
select train_number, url
from train_header
where timetable_id = ?
order by  train_order;
"""

COUNT_TRAIN_SQL = u"""
select count(train_number) cnt
from train_header
where timetable_id = ?
order by  train_order;
"""

SELECT_TRAIN_HEADER_SQL = u"""
select train_name, train_type, note, date, gou, url, train_type_prefix
from train_header
where timetable_id = ? and train_number = ? and url = ?
"""

SELECT_TRAIN_LIST_SQL = u"""
select train_number, train_name, train_type, note, gou, url, train_type_prefix, date
from train_header
where timetable_id = ?
order by train_order
"""

SELECT_TRAIN_NAME_LIST_SQL = u"""
select train_name
from train_header
where company = ? and railway_line = ? and updown = ?
order by  train_order;
"""

SELECT_TRAIN_GOU_LIST_SQL = u"""
select gou
from train_header
where company = ? and railway_line = ? and updown = ?
order by  train_order;
"""

SELECT_TRAIN_NOTE_LIST_SQL = u"""
select note
from train_header
where timetable_id = ?
order by train_order;
"""

SELECT_TRAIN_TIME_SQL = u"""
select train_arrival, train_departure
from train_time
where timetable_id =? and train_number = ? and url = ? and station_name = ?
order by station_order;
"""

#SELECT_TRAIN_STATIONS_SQL = u"""
#select station_order, count(station_name) cnt
#from train_time
#where timetable_id = ? and train_number = ? and url = ?
#order by station_order;
#"""

SELECT_TRAIN_ARRIVAL_LIST_SQL = u"""
select t.train_arrival
from train_header h left join train_time t on h.updown = t.updown and h.train_number = t.train_number and h.url = t.url
where h.timetable_id = ? and t.station_name = ?
order by h.train_order;
"""

SELECT_TRAIN_DEPARTURE_LIST_SQL = u"""
select t.train_departure
from train_header h left join train_time t on h.updown = t.updown and h.train_number = t.train_number and h.url = t.url
where h.timetable_id = ? and t.station_name = ?
order by h.train_order;
"""

SAME_STATION_ORDER_SQL = u"""
select station_order, count(station_name) cnt
from railway_line_station
where timetable_id = ? and station_name = ?
order by station_order;
"""

EXIST_TRAIN_URL_SQL = u"""
select count(url) cnt
from train_header
where timetable_id = ? and url = ?;
"""

SELECT_FIRST_STATION_SQL = u"""
select station_name
from railway_line_station
where timetable_id = ?
order by station_order
limit 1;
"""

SELECT_LAST_STATION_SQL = u"""
select station_name
from railway_line_station
where timetable_id = ?
order by station_order desc
limit 1;
"""

LINE_STATION_ORDER_MAX_SQL = u"""
select station_order
from railway_line_station
where timetable_id = ?
order by station_order desc
limit 1
"""
LINE_STATION_ORDER_LIST_ASC_SQL = u"""
select station_order
from railway_line_station
where timetable_id = ?
order by station_order asc
"""

LINE_STATION_ORDER_LIST_DESC_SQL = u"""
select station_order
from railway_line_station
where timetable_id = ?
order by station_order desc
"""

OFFSET_LINE_STATION_ORDER_SQL = u"""
update railway_line_station
set station_order = station_order + ?
where station_order = ? and timetable_id = ?
"""

EXIST_STATION_SQL = u"""
select station_name
from railway_line_station
where timetable_id = ? and station_name = ?;
"""

EXIST_SAME_TRAIN_SQL = u"""
select count(train_number) cnt
from train_header
where timetable_id = ? and train_number = ? and url = ?;
"""

SELECT_LINE_STATION_SQL = u"""
select station_name, display_arrival, display_departure
from railway_line_station
where timetable_id = ?
order by station_order asc
"""

SELECT_LINE_STATION_DESC_SQL = u"""
select station_name, display_arrival, display_departure
from railway_line_station
where timetable_id = ?
order by station_order desc
"""

MAX_TRAIN_ORDER_SQL = u"""
select train_order
from train_header
where timetable_id = ?
order by train_order desc;
"""

UPDATE_LINE_STATION_ARRIVAL_DEPARTURE_SQL = u"""
update railway_line_station
set display_arrival = (display_arrival or ?),
    display_departure = (display_departure or ?)
where timetable_id = ? and station_name = ?
"""

DELETE_TITLE_COLUMN_SQL = u"""
delete from title_column where timetable_id = ?;
"""

CHECK_TIMETABLE_SQL = u"""
select timetable_id
from timetable_attr
where company = ? and railway_line = ? and updown = ? and day = ?;
"""

# 2011/01/04 by H.Haga カーソルを途中で明示的にクローズする方法が不明の為、明示的に limit 1としておく
#MAX_TIMETABLE_ID_SQL = u"""
#select timetable_id
#from timetable_attr
#order by timetable_id desc
#limit 1
#
#"""

# 2011/01/04 by H.Haga カーソルを途中で明示的にクローズする方法が不明の為、明示的に limit 1としておく
MAX_TIMETABLE_ID_SQL = u"""
select timetable_id
from timetable_attr
order by timetable_id desc
"""

GET_TIMETABLE_SQL = u"""
select company, railway_line, updown, day, direction, note
from timetable_attr
where timetable_id = ?
"""

TIMETABLE_LIST_SQL = u"""
select timetable_id, company, railway_line, updown, day, direction, note
from timetable_attr
order by timetable_id
"""

LINE_STATION_LIST_SQL = u"""
select station_order, station_name, display_arrival, display_departure
from railway_line_station
where timetable_id = ?
order by station_order
"""

PAIR_TIMETABLE_SQL = u"""
select t.timetable_id, t.updown
from timetable_attr t inner join timetable_attr c on t.company = c.company and t.railway_line = c.railway_line and t.day = c.day
where c.timetable_id = ?
order by t.timetable_id
"""

DELETE_OUDIA_EKI_SQL = u"""
delete from oudia_eki
"""

INSERT_OUDIA_EKI_SQL = u"""
insert into oudia_eki (eki_order, eki_name, down_arrival, down_departure, up_arrival, up_departure)
values (?, ?, ?, ?, ?, ?)
"""

LAST_OUDIA_EKI_ORDER_SQL = u"""
select eki_order
from oudia_eki
order by eki_order desc
limit 1
"""

COUNT_OUDIA_EKI_SQL = u"""
select count(eki_order) cnt from oudia_eki
"""

INSERT_RAILWAY_LINE_STATION_TO_OUDIA_EKI_SQL = u"""
insert into oudia_eki (eki_order, eki_name, down_arrival, down_departure, up_arrival, up_departure)
select station_order, station_name, display_arrival, display_departure, null, null
from railway_line_station
where timetable_id = ?
"""

INSERT_RAILWAY_LINE_STATION_REVERSE_TO_OUDIA_EKI_SQL = u"""
insert into oudia_eki (eki_order, eki_name, down_arrival, down_departure, up_arrival, up_departure)
select ((select m.station_order
        from railway_line_station m
        where m.timetable_id = ? order by m.station_order desc limit 1) - s.station_order) * -1 reverse_order,
        s.station_name,
        s.display_arrival,
        s.display_departure,
        null,
        null
from railway_line_station s
where s.timetable_id = ?
"""

SELECT_FIRST_EKI_SQL = u"""
select eki_order,
       eki_name
from oudia_eki
order by eki_order
limit 1
"""

SELECT_LAST_EKI_SQL = u"""
select eki_order,
       eki_name
from oudia_eki
order by eki_order desc
limit 1
"""

EXIST_EKI_SQL = u"""
select eki_order
from oudia_eki
where eki_name = ?
order by eki_order
"""

# EXIST_EKI_WITH_CHK_ORDER_DOWN_SQL:EXIST_EKI_SQLのcheck order版 (down 方向)
EXIST_EKI_WITH_CHK_ORDER_DOWN_SQL = u"""
select eki_order
from oudia_eki
where eki_name = ? and
      eki_order > ?
order by eki_order
"""

# EXIST_EKI_WITH_CHK_ORDER_UP_SQL:EXIST_EKI_SQLのcheck order版 (up 方向)
EXIST_EKI_WITH_CHK_ORDER_UP_SQL = u"""
select eki_order
from oudia_eki
where eki_name = ? and
      eki_order < ?
order by eki_order
"""

UPDATE_EKI_SQL_PARTS = {'header':         u"update oudia_eki set ",
                        'down_arrival':   u"down_arrival = ?",
                        'down_departure': u"down_departure = ?",
                        'up_arrival':     u"up_arrival = ?",
                        'up_departure':   u"up_departure = ?",
                        'footer':         u" where eki_name = ?",
                        }

INSERT_EKI_SQL = u"""
insert into oudia_eki (eki_order, eki_name, down_arrival, down_departure, up_arrival, up_departure)
values (?, ?, ?, ?, ?, ?)
"""

SELECT_EKI_SQL = u"""
select eki_order,
       eki_name,
       down_arrival,
       down_departure,
       up_arrival,
       up_departure
from oudia_eki
order by eki_order
"""

SELECT_EKI_DESC_SQL = u"""
select eki_order,
       eki_name,
       down_arrival,
       down_departure,
       up_arrival,
       up_departure
from oudia_eki
order by eki_order desc
"""

#params timetable_id, train_number
#oudia_eki順(上りは逆順)にtrain_timeを取り出す(train_header別に)
#pair['down']を選んだかpair['up']を選んだ時点でupdownは分かっているので
#パラメータは timetable_id でよい。
SELECT_DOWN_EKI_TIME_SQL = u"""
select e.eki_order,
       e.eki_name,
       e.down_arrival,
       e.down_departure,
       t.station_order,
       t.station_name,
       t.train_arrival,
       t.train_departure
from oudia_eki e left join train_time t on e.eki_name = t.station_name
where t.timetable_id = ? and t.train_number = ? and t.url = ?
order by e.eki_order asc
"""

SELECT_UP_EKI_TIME_SQL = u"""
select e.eki_order,
       e.eki_name,
       e.up_arrival,
       e.up_departure,
       t.station_order,
       t.station_name,
       t.train_arrival,
       t.train_departure
from oudia_eki e left join train_time t on e.eki_name = t.station_name
where t.timetable_id = ? and t.train_number = ? and t.url = ?
order by e.eki_order desc
"""

# まずはユニークな一覧を得る
SELECT_TRAIN_DATE_NOTE_LIST_SQL = u"""
selct distinct date, note
from train_header
where timetable_id = ?
order by date, note
"""

SELECT_TRAIN_DATE_LIST_SQL = u"""
selct distinct date
from train_header
where timetable_id = ?
order by date
"""

# 路線駅名の重複度を算出。
# Loop処理するかどうかの判定基準となる。
COUNT_RAILWAY_LINE_STATION_SQL = u"""
select n.station_order, n.station_name, c.cnt
from
(select station_order, station_name
 from railway_line_station
 where timetable_id = 0 ) n inner join
(select station_name, count(station_name) cnt
 from railway_line_station
 where timetable_id = 0
 group by station_name) c on n.station_name = c.station_name
"""

# pick up loop station

#停車駅名の重複度を算出
# Loop処理するかどうかの判定基準となる。
#同一駅名があればその駅名の出現数をcntに。
#cntが1の駅名は従来どおりのマッチング方法で処理。
#cntが2以上の駅名はLOOP対応のマッチング方法で処理。
COUNT_OUDIA_EKI__SQL = u"""
select e.eki_order,
       e.eki_name,
       e.down_arrival,
       e.down_departure,
       e.up_arrival,
       e.up_departure,
       c.cnt
from oudia_eki e inner join
(select t.eki_name,
        count(t.eki_name) cnt
 from oudia_eki t
 group by t.eki_name) c
on e.eki_name = c.eki_name
order by e.eki_order
"""

GET_OUDIA_EKI_SAME_NAMES_SQL = u"""
select eki_order
from oudia_eki
where eki_name = ?
"""

INSERT_TRUE_TRAIN_STOP_SQL = u"""
insert into true_train_stop(timetable_id, train_number, url, station_order, eki_order)
values(?, ?, ?, ?, ?);
"""

#下記のSQLはsqliteのカーソルが両方向に対応してない為のものであるが、
#毎回SELECTするのも非効率なので最初にまるっとarrayに上げておく
#-- checking previous and forward record with both direction cursor. ... not implemented previous cursor (T_T)
#-- elect order asc and desc.
#-- or, eki_order and eki_name store to array.
#
#-- get post line eki
#select e.eki_order,
#       e.eki_name
#from oudia_eki e
#where e.eki_order > (select s.eki_order
#                     from oudia_eki s
#                     where s.eki_name = ?)
#order by e.eki_order asc
#
#-- get previous line eki
#select e.eki_order,
#       e.eki_name
#from oudia_eki e
#where e.eki_order < (select s.eki_order
#                     from oudia_eki s
#                     where s.eki_name = ?)
#order by e.eki_order desc
#
#-- once search, memory this pattern, and use this memory present agein.

#-----------------


class EkikaraDb:
    u""" database I/O

    implement SQL only this class.
    """
    def __init__(self, logger, filename=':memory:'):
        u""" contractor.
        """
        self.filename = filename
        self.logger = logger
        self.pp = pprint.PrettyPrinter(indent=2)
        try:
            self.conn = sqlite3.connect(self.filename)
        except sqlite3.OperationalError:
            self.conn.row_factory = sqlite3.Row
            #self.conn.isolation_level = None        #auto commit mode
        else:
            self.conn.row_factory = sqlite3.Row
            #self.conn.isolation_level = None        #auto commit mode
            try:
                self.conn.execute(TIMETABLE_ATTR_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TIMETABLE_ATTR_INDEX_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(LINE_STATION_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TRAIN_HEADER_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TRAIN_HEADER_INDEX_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TRAIN_TIME_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TRAIN_TIME_INDEX_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TITLE_COLUMN_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(OUDIA_EKI_SQL)        # work table for OuDia fromat
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(OUDIA_EKI_INDEX_SQL)
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()
            try:
                self.conn.execute(TRUE_TRAIN_STOP_SQL)        # work table for OuDia fromat loop line matching
            except sqlite3.OperationalError as message:
                self.logger.debug("message=%s" % (message))
            finally:
                self.conn.commit()

    def close(self):
        try:
            self.conn.close()
        except sqlite3.Error, e:
            self.logger.debug(u"EkikaraDB::close();An error occurred:", e.args[0])
        #finally:
        #    pass

    def __del__(self):
        self.close()

    def getTimetableId(self, company, railway_line, updown, day):
        u""" specify timetable_id from timetable_attr
        """
        assert updown == 'up' or updown == 'down', "updown is not up or down"
        check_rows = self.conn.execute(CHECK_TIMETABLE_SQL, (company, railway_line, updown, day))
        check_row = check_rows.fetchone()
        if check_row and check_row[0] != None:
            return check_row[0]
        else:
            return None

    def addTimetableAttr(self, company, railway_line, updown, day, direction=None, note=None):
        u"""already exist, return None. else added, return timetable_id and set to EkikaraDb.target_timetable
        """
        assert company and (updown == 'up' or updown == 'down'), "company is None or, updown is not up or down"
        check_id = self.getTimetableId(company, railway_line, updown, day)
        if check_id != None:
            return check_id  # already exists.
        max_rows = self.conn.execute(MAX_TIMETABLE_ID_SQL)
        max_row = max_rows.fetchone()
        if max_row and max_row['timetable_id'] != None:
            new_id = max_row['timetable_id'] + 1
        else:
            new_id = 0  # first id
        max_rows.close()        # 途中辞めにしたカーソルは close しないと その後 commit が使えない
        self.logger.debug("execute INSERT_TIMETABLE_ATTR_SQL[%d][%s][%s][%s][%s][%s][%s]" % (new_id,
                                                      company,
                                                      railway_line,
                                                      updown,
                                                      day,
                                                      direction,
                                                      note,))
        self.conn.execute(INSERT_TIMETABLE_ATTR_SQL, (new_id,
                                                      company,
                                                      railway_line,
                                                      updown,
                                                      day,
                                                      direction,
                                                      note,))
        self.conn.commit()
        return new_id

    def timetableList(self):
        u""" get all row in timetable_attr order by timetable_id

        return; cursor object
        """
        return self.conn.execute(TIMETABLE_LIST_SQL)

    def lineStationList(self, timetable_id):
        u""" get specifiied timetable_id's row in railway_line_station

        return; cursor object
        """
        return self.conn.execute(LINE_STATION_LIST_SQL, (timetable_id,))        # one tupple

    def pairTimetable(self, timetable_id):
        u""" get pair timetable.

        return; { 'down': <<timetable_id>>, 'up': <<timetable_id>> }
        上りまたは下りいずれかを指定すると、上り下りのペアを返す。
        但し、該当するのが無いときは、その部分は None
        """
        result = {'down': None, 'up': None}
        for row in self.conn.execute(PAIR_TIMETABLE_SQL, (timetable_id, )):
            if row:
                if row['updown'] == 'down':
                    result['down'] = row['timetable_id']
                elif row['updown'] == 'up':
                    result['up'] = row['timetable_id']
                else:
                    self.logger.error("unknown updown=%s(id=%d)" % (row['updown'], row['timetable_id']))
        return result

    def clearOuDiaEki(self):
        u"""ワークテーブル oudia_eki をクリア
        """
        self.conn.execute(DELETE_OUDIA_EKI_SQL)
        self.conn.commit()

    def countOuDiaEki(self):
        row = self.conn.execute(COUNT_OUDIA_EKI_SQL).fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return 0

    def listOuDiaEki(self, direction=u"down"):
        u""" iterator for oudaia_eki
        """
        if direction == u"up":
            for row in self.conn.execute(SELECT_EKI_DESC_SQL):
                line = {'eki_order': row[0],
                        'eki_name': row[1],
                        'down_arrival': row[2],
                        'down_departure': row[3],
                        'up_arrival': row[4],
                        'up_departure': row[5]}
                yield line
        else:  # down and default
            for row in self.conn.execute(SELECT_EKI_SQL):
                line = {'eki_order': row[0],
                        'eki_name': row[1],
                        'down_arrival': row[2],
                        'down_departure': row[3],
                        'up_arrival': row[4],
                        'up_departure': row[5]}
                yield line

    def clearTrueTrainStop(self):
        self.conn.execute(DELETE_TRUE_TRAIN_STOP_SQL)
        self.conn.commit()

    def insertOuDiaEki(self, eki_order, eki_name, down_arrival, down_departure, up_arrival, up_departure, byCommit=True):
        self.conn.execute(INSERT_OUDIA_EKI_SQL,
                          (eki_order,
                           eki_name,
                           down_arrival,
                           down_departure,
                           up_arrival,
                           up_departure))
        if byCommit:
            self.conn.commit()

    def addOuDiaEki(self, eki_name, down_arrival, down_departure, up_arrival, up_departure, byCommit=True):
        new_eki_order = 1
        cur = self.conn.execute(LAST_OUDIA_EKI_ORDER_SQL)
        row = cur.fetchone()
        if row:
            new_eki_order = row['eki_order'] + 1
        cur.close()
        self.insertOuDiaEki(new_eki_order, eki_name, down_arrival, down_departure, up_arrival, up_departure, byCommit)


class Timetable:
    def __init__(self, db, target_timetable_id, logger):
        self.db = db   # refer EkikaraDb
        self.target_timetable = target_timetable_id     # timetable_attr.timetable_id
        self.logger = logger       # logging object
        self.pp = pprint.PrettyPrinter(indent=2)

    def getAttr(self):
        row = self.db.conn.execute(GET_TIMETABLE_SQL, (self.target_timetable, )).fetchone()      # one element tuple
        if row:
            return {'company':      row['company'],
                    'railway_line': row['railway_line'],
                    'updown':       row['updown'],
                    'day':          row['day'],
                    'direction':    row['direction'],
                    'note':         row['note'],
                    }
        else:
            self.logger.error(u"target_timetable can not get attr. id=[%s]" % (self.target_timetable,))
            raise InvalidTimetableError.InvalidTimetableError(self.target_timetable)

    def getId(self):
        return self.target_timetable

    def setLineStation(self, stationList):
        if self.getFirstStation():     # このタイムテーブルですでに駅名リストが存在している?
            self.mergeLineStation(stationList)
        else:
            self.extendLineStation(stationList)

    def stationOrderMax(self):
        u"""return max station order

        if row is nothing, return -1. because alway use next value. (-1 next value is zero)
        if return zero, next use is 1. but first index is 0.
        """
        row = self.db.conn.execute(LINE_STATION_ORDER_MAX_SQL, (self.target_timetable, )).fetchone()  # one element tuple
        if row != None and row[0] != None:
            return row[0]
        else:
            return -1

    def stationOrderListAsc(self):
        result = []
        for row in  self.db.conn.execute(LINE_STATION_ORDER_LIST_ASC_SQL, (self.target_timetable, )):
            result.append(row[0])
        return result

    def stationOrderListDesc(self):
        result = []
        for row in self.db.conn.execute(LINE_STATION_ORDER_LIST_DESC_SQL, (self.target_timetable, )):
            result.append(row[0])
        return result

    def offsetStationOrder(self, offset):
        self.logger.debug("offsetStationOrder() offset=%d, target=%d" % (offset, self.target_timetable,))
        station_order_list = []
        if offset > 0:
            station_order_list = self.stationOrderListDesc()    # 後ろから順番に
        elif offset < 0:
            station_order_list = self.stationOrderListAsc()     # 前から順番に
        else:
            pass
        for station_order in station_order_list:       # listが空だったら何もしない(offset == 0)
            self.db.conn.execute(OFFSET_LINE_STATION_ORDER_SQL, (offset, station_order, self.target_timetable))
        self.db.conn.commit()       # 全てupdateしてからcommit

    def extendLineStation(self, stationList):
        index = self.stationOrderMax() + 1
        for station_order, station in enumerate(stationList):
            display_arrival = 0
            display_departure = 0
            if 'arrival' in station  and station['arrival']:
                display_arrival = 1
            if 'departure' in station and station['departure']:
                display_departure = 1
            self.db.conn.execute(INSERT_RAILWAY_LINE_STATION_SQL, (self.target_timetable, index, station['name'],  display_arrival, display_departure))
            index += 1
        self.db.conn.commit()

    def topInsertLineStation(self, stationList):
        u""" stationList to insert railway_line_station top

        """
        self.offsetStationOrder(len(stationList))
        index = 0
        for station_order, station in enumerate(stationList):
            display_arrival = 0
            display_departure = 0
            if 'arrival' in station  and station['arrival']:
                display_arrival = 1
            if 'departure' in station and station['departure']:
                display_departure = 1
            self.db.conn.execute(INSERT_RAILWAY_LINE_STATION_SQL, (self.target_timetable,
                                                                   index,
                                                                   station['name'],
                                                                   display_arrival,
                                                                   display_departure))
            index += 1
        self.db.conn.commit()

    def getFirstStation(self):
        u""" return first station name or not on this target.

        return ; first station name or None ( no List)
        if return None, but table not empty. maybe other line stations exists.
        """
        rows = self.db.conn.execute(SELECT_FIRST_STATION_SQL, (self.target_timetable, ))
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return None

    def getLastStation(self):
        rows = self.db.conn.execute(SELECT_LAST_STATION_SQL, (self.target_timetable, ))
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return None

    def isExistStation(self, station_name):
        rows = self.db.conn.execute(EXIST_STATION_SQL, (self.target_timetable, station_name))
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return None

    def updateLineStation(self, stationList):
        u"""update railway_line_station.display_arrival, display_departure by stationList
        """
        for station in stationList:
            if 'arrival' in station:
                arrival = station['arrival']
            else:
                arrival = 0
            if 'departure' in station:
                departure = station['departure']
            else:
                departure = 0
            self.db.conn.execute(UPDATE_LINE_STATION_ARRIVAL_DEPARTURE_SQL, (arrival,
                                                                             departure,
                                                                             self.target_timetable,
                                                                             station['name']))
        self.db.conn.commit()

    def mergeLineStation(self, stationList):
        u"""
        merge stationList and reilway_line_station

        両方のリストに接点が無い場合はアボート
        """
        mapLine = {}
        for i, v in enumerate(stationList):
            mapLine[v['name']] = i
        # trainのリストとマージする場合も
        # 時刻表ページのリストとマージする。
        # 時刻表ページの詳細から取得した列車のリストなのだから、時刻表の駅とは必ず接点があるはず
        # しかし時刻表ページ同士が結びつくのを保証するものではないのだが......
        if stationList[0]['name'] == self.getFirstStation():
            if self.isExistStation(stationList[-1]['name']):
                # listBはlistAと同一か、短い→追加の必要は無い
                self.updateLineStation(stationList)
            else:
                self.updateLineStation(stationList[:mapLine[self.getLastStation()] + 1])
                # listBはlistAより長い→listAより長い分を追加
                # listBの、listAの最後の駅名のindexを求める。その次からをappend
                self.extendLineStation(stationList[mapLine[self.getLastStation()] + 1:])
        elif self.isExistStation(stationList[0]['name']):
            # listBはlistAの途中から始まっている
            if self.isExistStation(stationList[-1]['name']):
                # listBはlistAの途中から始まり、listAより短い→追加不要
                self.updateLineStation(stationList)
            else:
                self.updateLineStation(stationList[:mapLine[self.getLastStation()] + 1])
                # listBはlistAの途中から始まり、listAより長い→listAより長い部分を追加
                self.extendLineStation(stationList[mapLine[self.getLastStation()] + 1:])
        elif self.isExistStation(stationList[-1]['name']):
            # listB は list Aより上にズレている。→listAより上にズレている部分をlistAの前に追加
            self.topInsertLineStation(stationList[:mapLine[self.getFirstStation()] - 1])
            self.updateLineStation(stationList[mapLine[self.getFirstStation()]:])
        else:
            self.logger.error("railway_line_station table and stationList can not merge.")
            # 全く重なっていない
            # どちらが前か分からない。
            # ユーザに繋がる区間を指定するようエラーを出して終了
            raise MergeStationError.MergeStationError(self.db, stationList)

    def lineStation(self):
        return self.db.conn.execute(SELECT_LINE_STATION_SQL, (self.target_timetable, ))

    def lineStationReverse(self):
        return self.db.conn.execute(SELECT_LINE_STATION_DESC_SQL, (self.target_timetable, ))

    def setTitleColumn(self):
        self.db.conn.execute(DELETE_TITLE_COLUMN_SQL, (self.target_timetable, ))  # sqlite not hub truncate...
        row = 0
        for item in (u"列車番号", u"列車種別", u"列車名", u"号数", u"gou_name", ):
            row += 1
            self.db.conn.execute(INSERT_TITLE_COLUMN_SQL, (self.target_timetable, row, item,  u"", None))
        station_index = -1
        for station in  self.lineStation():
            if station['display_arrival'] and station['display_arrival'] == 1:
                row += 1
                station_index += 1
                self.db.conn.execute(INSERT_TITLE_COLUMN_SQL, (self.target_timetable,
                                                               row,
                                                               station['station_name'],
                                                               u"着",
                                                               station_index))
            if station['display_departure'] and station['display_departure'] == 1:
                row += 1
                station_index += 1
                self.db.conn.execute(INSERT_TITLE_COLUMN_SQL, (self.target_timetable,
                                                               row,
                                                               station['station_name'],
                                                               u"発",
                                                               station_index))
        # set note
        row += 1
        self.db.conn.execute(INSERT_TITLE_COLUMN_SQL, (self.target_timetable, row, u"備考",  u"", None))
        self.db.conn.commit()

    def isExistSameTrain(self, train_number, url):
        u"""If exist same train number, return 1 or over, else None
        """
        rows = self.db.conn.execute(EXIST_SAME_TRAIN_SQL, (self.target_timetable, train_number, url))
        row = rows.fetchone()
        if row and row[0] != None and row[0] > 0:
            return row[0]
        else:
            return None

    def getMaxTrainOrder(self):
        rows = self.db.conn.execute(MAX_TRAIN_ORDER_SQL, (self.target_timetable, ))
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return -1

    def setTrain(self, order, train):
        assert 'number' in train and train['number'], "nothing train number"
        if not 'name' in train:
            train['name'] = None
        if not 'type' in train:
            train['type'] = None
        if not 'note' in train:
            train['note'] = None
        if not 'date' in train:
            train['date'] = None
        if not 'gou' in train:
            train['gou'] = None
        if not 'url' in train:
            train['url'] = None
        if not 'type_prefix' in train:
            train['type_prefix'] = None
        self.db.conn.execute(INSERT_TRAIN_HEADER_SQL, (self.target_timetable,
                                                       train['number'],
                                                       order,      # train order
                                                       train['name'],
                                                       train['type'],
                                                       train['note'],
                                                       train['date'],
                                                       train['gou'],
                                                       train['url'],
                                                       train['type_prefix']))
        for stationOrder, station in enumerate(train['stations']):
            if not 'name' in station:
                station['name'] = None
            if not 'arrival' in station:
                station['arrival'] = None
            if not 'departure' in station:
                station['departure'] = None
            if not 'platform' in station:
                station['platform'] = None
            self.db.conn.execute(INSERT_TRAIN_TIME_SQL, (self.target_timetable,
                                                         train['number'],
                                                         train['url'],
                                                         stationOrder,
                                                         station['name'],
                                                         station['arrival'],
                                                         station['departure'],
                                                         station['platform']))
        self.db.conn.commit()

    def trainNumberList(self):
        resultList = []
        for row in self.db.conn.execute(SELECT_TRAIN_NUMBER_LIST_SQL, (self.target_timetable, )):      # one element tuple
            resultList.append({'train_number': row[0], 'url': row[1]})
        return resultList

    def trainHeaderList(self, trainNumberList):
        header = {'name': [],
                  'type': [],
                  'note': [],
                  'date': [],
                  'gou': [],
                  'gou_name': [],
                  'url': [],
                  'type_prefix': [], }
        for number in trainNumberList:
            rows = self.db.conn.execute(SELECT_TRAIN_HEADER_SQL, (self.target_timetable,
                                                                  number['train_number'],
                                                                  number['url'],))
            row = rows.fetchone()
            if row:
                if row['train_name'] != None:
                    header['name'].append(row['train_name'])
                else:
                    header['name'].append(u"")
                if row['train_type'] != None:
                    header['type'].append(row['train_type'])
                    header['type_prefix'].append(row['train_type_prefix'])
                else:
                    header['type'].append(u"")
                    header['type_prefix'].append(u"")
                if row['note'] != None:
                    header['note'].append((row['note'].strip()).replace(u"\n", u""))
                else:
                    header['note'].append(u"")
                if row['gou'] != None:
                    header['gou'].append(row['gou'])
                    header['gou_name'].append(u"号")
                else:
                    header['gou'].append(u"")
                    header['gou_name'].append(u"")
                if row['url'] != None:
                    header['url'].append(row['url'])
                else:
                    header['url'].append(u"")
                if row['date'] != None:        # 運転日(text)
                    header['date'].append(row['date'])
                else:
                    header['date'].append(u"")
            else:
                header['name'].append(u"")
                header['type'].append(u"")
                header['note'].append(u"")
                header['gou'].append(u"")
                header['gou_name'].append(u"")
                header['url'].append(u"")
                header['type_prefix'].append(u"")
                header['date'].append(u"")
        return header

    def countTrain(self):
        count = 0
        obj = self.db.conn.execute(COUNT_TRAIN_SQL, (self.target_timetable, ))       # one element tuple
        if obj and ('cnt' in obj):
            count = obj['cnt']
        return count

    def trainTime(self, number, url, station):
        rows = self.db.conn.execute(SELECT_TRAIN_TIME_SQL, (self.target_timetable, number, url, station))
        return rows.fetchone()

    #def trainTimeList(self, number, url):
    #    return self.db.conn.execute(SELECT_TRAIN_STATIONS_SQL, (self.target_timetable, number, url))

    def trainArrivalList(self, station_name, trainNumberList):
        resultList = []
        for number in trainNumberList:
            train = self.trainTime(number['train_number'], number['url'], station_name)
            if train and train['train_arrival'] != None:
                resultList.append(train['train_arrival'].replace(u":", u""))
            else:
                resultList.append(u"")
        return resultList

    def trainDepartureList(self, station_name, trainNumberList):
        resultList = []
        for number in trainNumberList:
            train = self.trainTime(number['train_number'], number['url'], station_name)
            if train and train['train_departure'] != None:
                resultList.append(train['train_departure'].replace(u":", u""))
            else:
                resultList.append(u"")
        return resultList

    def isExistTrainUrl(self, url):
        rows = self.db.conn.execute(EXIST_TRAIN_URL_SQL, (self.target_timetable, url))
        row = rows.fetchone()
        if row and row[0] != None and row[0] > 0:
            return row[0]
        else:
            return None

    def getTitleColumn(self):
        return self.db.conn.execute(SELECT_TITLE_COLUMN_SQL, (self.target_timetable, ))

    def putOuDiaCSV(self):
        trainNumberList = self.trainNumberList()
        header = self.trainHeaderList(trainNumberList)       # trainNumberList をカスタマイズすればそれに従ったリストを出す
        for row in self.getTitleColumn():
            line = [row['title'], row['arrival_departure'], ]
            if row['station_index'] != None:
                # station name
                if row['arrival_departure'] == u"着":
                    line += self.trainArrivalList(row['title'], trainNumberList)
                elif row['arrival_departure'] == u"発":
                    line += self.trainDepartureList(row['title'], trainNumberList)
                else:
                    self.logger.error("unknown arrival departure [%s]" % (row['arrival_departure']))
            else:
                if row['title'] == u"列車番号":
                    line += trainNumberList
                elif row['title'] == u"列車種別":
                    line += header['type']
                elif row['title'] == u"列車名":
                    line += header['name']
                elif row['title'] == u"号数":
                    line += header['gou']
                elif row['title'] == u"gou_name":
                    line = [u"", row['arrival_departure'], ]    # 号名だけタイトルcolumn無し
                    line += header['gou_name']
                elif row['title'] == u"備考":
                    line += header['note']
                else:
                    self.logger.error("unknown arrival item [%s]" % (row['title']))
            yield line

    def getFirstEki(self):
        u""" if exist then return eki_order, else None
        """
        rows = self.db.conn.execute(SELECT_FIRST_EKI_SQL)
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return None

    def getFirstEkiName(self):
        u""" if exist then return eki_name, else None
        """
        rows = self.db.conn.execute(SELECT_FIRST_EKI_SQL)
        row = rows.fetchone()
        if row and row[0] != None:
            return row[1]       # 0:eki_order, 1:eki_name
        else:
            return None

    def getLastEki(self):
        u""" if exit then return eki_order, else None
        """
        rows = self.db.conn.execute(SELECT_LAST_EKI_SQL)
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return None

    def getLastEkiName(self):
        u""" if exit then return eki_name, else None
        """
        rows = self.db.conn.execute(SELECT_LAST_EKI_SQL)
        row = rows.fetchone()
        if row and row[0] != None:
            return row[1]
        else:
            return None

    def isExistEki(self, ekiName, lastOrder=None, updown='down'):
        u""" if exit then return eki_order, else None
        """
        if lastOrder != None and updown == 'down':
            rows = self.db.conn.execute(EXIST_EKI_WITH_CHK_ORDER_DOWN_SQL, (ekiName, lastOrder))
        elif lastOrder != None and updown == 'up':
            rows = self.db.conn.execute(EXIST_EKI_WITH_CHK_ORDER_UP_SQL, (ekiName, lastOrder))
        else:
            rows = self.db.conn.execute(EXIST_EKI_SQL, (ekiName, ))    # one element tuple
        row = rows.fetchone()
        if row and row[0] != None:
            return row[0]
        else:
            return None

    def existEki(self, ekiName, lastOrder=None, updown='down'):
        u""" if exit then return eki_order, else
        """
        if lastOrder != None and updown == 'down':
            return self.db.conn.execute(EXIST_EKI_WITH_CHK_ORDER_DOWN_SQL, (ekiName, lastOrder))
        elif lastOrder != None and updown == 'up':
            return self.db.conn.execute(EXIST_EKI_WITH_CHK_ORDER_UP_SQL, (ekiName, lastOrder))
        else:
            return self.db.conn.execute(EXIST_EKI_SQL, (ekiName, ))    # one element tuple

    def updateEki(self, ekiList):
        for eki in ekiList:
            self.logger.debug(eki)
            update_parts = []
            update_values = []
            if 'down_arrival' in eki:
                update_parts.append(UPDATE_EKI_SQL_PARTS['dwon_arrival'])
                update_values.append(eki['down_arrival'])
            if 'down_departure' in eki:
                update_parts.append(UPDATE_EKI_SQL_PARTS['down_departure'])
                update_values.append(eki['down_departure'])
            if 'up_arrival' in eki:
                update_parts.append(UPDATE_EKI_SQL_PARTS['up_arrival'])
                update_values.append(eki['up_arrival'])
            if 'up_departure' in eki:
                update_parts.append(UPDATE_EKI_SQL_PARTS['up_departure'])
                update_values.append(eki['up_departure'])
            if len(update_parts) > 0:
                self.logger.debug(update_parts)
                update_sql = UPDATE_EKI_SQL_PARTS['header'] + ",".join(update_parts) + UPDATE_EKI_SQL_PARTS['footer']
                update_values.append(eki['name'])   # for footer
                self.logger.debug(u"updateEki()=[%s]" % (update_sql))
                self.logger.debug(update_values)
                self.db.conn.execute(update_sql, update_values)
        self.db.conn.commit()

    def insertEki(self, ekiList, offset=0):
        index = offset - 1
        for eki in ekiList:
            index += 1
            values = [index]
            values.append(eki['name'])
            if 'down_arrival' in eki and eki['down_arrival'] != None:
                values.append(eki['down_arrival'])
            else:
                values.append(None)
            if 'down_departure' in eki and eki['down_departure'] != None:
                values.append(eki['down_departure'])
            else:
                values.append(None)
            if 'up_arrival' in eki and eki['up_arrival'] != None:
                values.append(eki['up_arrival'])
            else:
                values.append(None)
            if 'up_departure' in eki and eki['up_departure'] != None:
                values.append(eki['up_departure'])
            else:
                values.append(None)
            self.db.conn.execute(INSERT_EKI_SQL, values)
        self.db.conn.commit()

    def extendEki(self, ekiList):
        self.insertEki(ekiList, self.getLastEki())

    def topInsertEki(self, ekiList):
        self.insertEki(ekiList, len(ekiList))

    def mergeEki(self, ekiList):
        u"""自分自身の持つ駅リストと与えられた駅リストの合成を試みる。

        両方のリストに接点が無い場合はアボート
        """
        mapEki = {}
        for i, v in enumerate(ekiList):
            mapEki[v['name']] = i
        # trainのリストとマージする場合も
        # 時刻表ページのリストとマージする。
        # 時刻表ページの詳細から取得した列車のリストなのだから、時刻表の駅とは必ず接点があるはず
        # しかし時刻表ページ同士が結びつくのを保証するものではないのだが......
        if ekiList[0]['name'] == self.getFirstEkiName():
            if self.isExistEki(ekiList[-1]['name']):
                # listBはlistAと同一か、短い→追加の必要は無い
                self.updateEki(ekiList)
            else:
                self.updateEki(ekiList[:mapEki[self.getLastEkiName()] + 1])
                # listBはlistAより長い→listAより長い分を追加
                # listBの、listAの最後の駅名のindexを求める。その次からをappend
                self.extendEki(ekiList[mapEki[self.getLastEkiName()] + 1:])
        elif self.isExistEki(ekiList[0]['name']):
            # listBはlistAの途中から始まっている
            if self.isExistEki(ekiList[-1]['name']):
                # listBはlistAの途中から始まり、listAより短い→追加不要
                self.updateEki(ekiList)
            else:
                self.updateEki(ekiList[:mapEki[self.getLastEkiName()] + 1])
                # listBはlistAの途中から始まり、listAより長い→listAより長い部分を追加
                self.extendEki(ekiList[mapEki[self.getLastEkiName()] + 1:])
        elif self.isExistStation(ekiList[-1]['name']):
            # listB は list Aより上にズレている。→listAより上にズレている部分をlistAの前に追加
            self.topInsertEki(ekiList[:mapEki[self.getFirstEkiName()] - 1])
            self.updateEki(ekiList[mapEki[self.getFirstEkiName()]:])
        else:
            self.logger.error("oudia_eki table and ekiList can not merge.")
            raise MergeEkiError.MergeEkiError(self.db, ekiList)

    def setToOuDiaEki(self):
        u"""駅情報を oudaia_eki へセット

        下り(down)の場合はそのままセット
        上り(up)の場合は逆順でセット
        新規にセットする場合は EkikaraDb.clearOudiaEki()を実行すること。
        空で無いテーブルにセットしようとした場合はマージとなる
        同じ路線でupdown誤ってい指定してある時はどうなっても知らん。

        offset + down or reverse to set .... ( no need merge)
        merge part is update by for - loop ?
        """
        attr = self.getAttr()
        if self.db.countOuDiaEki() > 0:
            # マージ
            eki = []
            if attr['updown'] == 'down':
                for station in self.lineStation():
                    item = {'name': station['station_name'],
                            'down_arrival': station['display_arrival'],
                            'down_departure': station['display_departure']}
                    eki.append(item)
            else:
                for station in self.lineStationReverse():
                    item = {'name': station['station_name'],
                            'up_arrival': station['display_arrival'],
                            'up_departure': station['display_departure']}
                    eki.append(item)
            #前後関係を知り、それにしたがって追加+更新する
            self.mergeEki(eki)
        else:
            # テーブルが空なのでINSRTするだけ。upは逆順で
            if attr['updown'] == 'down':
                self.db.conn.execute(INSERT_RAILWAY_LINE_STATION_TO_OUDIA_EKI_SQL, (self.getId(),))    # one tuple
                self.db.conn.commit()
            else:
                self.db.conn.execute(INSERT_RAILWAY_LINE_STATION_REVERSE_TO_OUDIA_EKI_SQL, (self.getId(), self.getId()))
                self.db.conn.commit()

#    def prepareEkiTimeCursor(self, train_number, url):
#        pass

    def ekiTimeCursor(self, train_number, url):
        attr = self.getAttr()
        if attr['updown'] == 'down':
            return self.db.conn.execute(SELECT_DOWN_EKI_TIME_SQL, (self.getId(), train_number, url))      # one tuple
        elif attr['updown'] == 'up':
            return self.db.conn.execute(SELECT_UP_EKI_TIME_SQL, (self.getId(), train_number, url))      # one tuple
        else:
            raise InvalidTimetableError.InvalidTimetableError(self.getId())

#    def getTrainAttr(self, number, url):
#        header = {}
#        rows = self.db.conn.execute(SELECT_TRAIN_HEADER_SQL, (self.target_timetable, number, url))
#        row = rows.fetchone()
#        if row:
#            if row['train_name'] != None:
#                header['name'].append(row['train_name'])
#            else:
#                header['name'].append(u"")
#            if row['train_type'] != None:
#                header['type'].append(row['train_type'])
#                header['type_prefix'].append(row['train_type_prefix'])
#            else:
#                header['type'].append(u"")
#                header['type_prefix'].append(u"")
#            if row['note'] != None:
#                header['note'].append((row['note'].strip()).replace(u"\n", u""))
#            else:
#                header['note'].append(u"")
#            if row['gou'] != None:
#                header['gou'].append(row['gou'])
#                header['gou_name'].append(u"号")
#            else:
#                header['gou'].append(u"")
#                header['gou_name'].append(u"")
#            if row['url'] != None:
#                header['url'].append(row['url'])
#            else:
#                header['url'].append(u"")
#            return header
#        else:
#            return None

    def trainList(self):
        return self.db.conn.execute(SELECT_TRAIN_LIST_SQL, (self.target_timetable,))    # one tuple

    def trainDateNoteList(self):
        return self.db.conn.execute(SELECT_TRAIN_DATE_NOTE_LIST_SQL, (self.target_timetable, ))   # one tuple

    def trainDateList(self):
        return self.db.conn.execute(SELECT_TRAIN_DATE_LIST_SQL, (self.target_timetable, ))   # one tuple

    def setTrueTrainStop(self, train_number, url, station_order, eki_order, byCommit=True):
        self.logger.debug(u"target_timetable=%d,setTrueTrainStop(train_number=%s, url=%s, station_order=%d, eki_order=%d" %
                          (self.target_timetable, train_number, url, station_order, eki_order))
        self.db.conn.execute(INSERT_TRUE_TRAIN_STOP_SQL, (self.target_timetable,
                                                          train_number,
                                                          url,
                                                          station_order,
                                                          eki_order))
        if byCommit:
            self.db.conn.commit()
